Team , Thanks for your help in advance !

I ran an alter statement against a table object

ALTER TABLE TEST
ADD NAME3 VARCHAR(50)

I can review the statement within the trace under TextData Column  -

when i run this

DECLARE @TraceFileName VARCHAR(3000)
SELECT @TraceFileName = PATH FROM sys.traces WHERE is_default = 1

SELECT 
ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
, spid
, ClientProcessID
, HostName
,TextData 
FROM ::fn_trace_gettable( @TraceFileName, DEFAULT )
WHERE 
objecttype not in (21587)
AND EventClass in (46,47,164) 
AND EventSubclass = 0 
AND LoginName NOT IN ('NT AUTHORITY\NETWORK SERVICE', 'sa') 
AND DatabaseID <> 2

The textdata column  reports NULL instead I'm looking out for complete Alter statement  , I'm trying on finding a way to capture alter , delete and Create statements from TRACE .

Thanks again .

Yep, this column returns NULL.  One method is to use SQL Server profiler to insert the captured  data to the table directly...

or by using even klass  to identify an ALTER opration

One method is to use SQL Server profiler to insert the captured  data to the table directly...

Thanks Uri .

How ??

Yep, this column returns NULL.  One method is to use SQL Server profiler to insert the captured  data to the table directly...

or by using even klass  to identify an ALTER opration

use A

CREATE TABLE TEST (c int)
ALTER TABLE TEST
ADD NAME3 VARCHAR(50)

begin try declare @enable int 
-- Check to find out if Default Server Side traces are running 
select top 1 @enable = convert(int,value_in_use) 
from sys.configurations where name = 'default trace enabled' if @enable = 1 
--default trace is enabled 
begin declare @d1 datetime; declare @diff int; 
declare @curr_tracefilename varchar(500); 
declare @base_tracefilename varchar(500);
 declare @indx int ; 
declare @temp_trace table (txt varchar(100),obj_name nvarchar(256) collate database_default, database_name nvarchar(256) collate database_default , start_time datetime, event_class int, event_subclass int, object_type int, server_name nvarchar(256) collate database_default , login_name nvarchar(256) collate database_default, application_name nvarchar(256) collate database_default, ddl_operation nvarchar(40) collate database_default );
 select @curr_tracefilename = path from sys.traces where is_default = 1 ; 
set @curr_tracefilename = reverse(@curr_tracefilename) 
select @indx = PATINDEX('%\%', @curr_tracefilename) 
set @curr_tracefilename = reverse(@curr_tracefilename)
 set @base_tracefilename = LEFT( @curr_tracefilename,len(@curr_tracefilename) - @indx) + '\log.trc'; 
insert into @temp_trace select TextData,ObjectName,
 DatabaseName, StartTime, EventClass, EventSubClass,
 ObjectType, ServerName, LoginName, ApplicationName,
 'temp' from ::fn_trace_gettable( @base_tracefilename, default )
 where EventClass in (164) and EventSubclass = 0 and DatabaseID <> 2 /*
Event ID 164: Object:Altered If you need to
 track one particular event then you can modify the filters
 above To track only Object: 
Deleted Events, the WHERE clause would be: where EventClass in (47) 
and EventSubclass = 0 and DatabaseID <> 2 */ 

update @temp_trace set ddl_operation = 'ALTER' 
where event_class = 164 select @d1 = min(start_time)
 from @temp_trace set @diff= datediff(hh,@d1,getdate())
 set @diff=@diff/24; select @diff as difference, @d1 as date,
 object_type as obj_type_desc , * from @temp_trace where object_type
 not in (21587) order by start_time desc /* The object type decides
 what type of object is being modified. Let's say 
we wanted to find out only DROP DATABASE events, then we
 can add an additional filter as follows to the above
 SELECT statement: and object_type = 16964 -- This is a database */ 
end else begin print 'Default server side traces not enabled'
 end end try begin catch select -100 as difference, ERROR_NUMBER() as date, 
ERROR_SEVERITY() as obj_type_desc, ERROR_STATE() as obj_name, ERROR_MESSAGE() as database_name, 1 as start_time,
 1 as event_class, 1 as event_subclass, 1 as object_type,
 1 as server_name, 1 as login_name, 1 as application_name, 1 as ddl_operation 
end catch 

USE [TEST_R_D]
GO

/****** Object:  StoredProcedure [dbo].[dba_TrackSchemaChanges]    Script Date: 2/16/2015 6:34:25 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[dba_TrackSchemaChanges] ( @Server sysname )AS
--DECLARE VARIABLES
BEGIN

DECLARE @TraceFileName varchar (500)
DECLARE @indx int 
DECLARE @SQL varchar(750)
DECLARE @Cnt int
END

--SET VARIABLES
BEGIN
/* Fetch default trace file path */
    SELECT @TraceFileName = PATH FROM sys.traces WHERE is_default = 1
END

--CREATE TEMP TABLE
BEGIN
DECLARE @TmpTrace TABLE (
obj_name nvarchar(256) COLLATE database_default
, database_name nvarchar(256) COLLATE database_default
, start_time datetime
, event_class int
, event_subclass int
, object_type int
, server_name nvarchar(256)-- COLLATE  database_default
, login_name nvarchar(256)-- COLLATE  database_default
, application_name nvarchar(256)-- COLLATE  database_default
, ddl_operation nvarchar(max)-- COLLATE  database_default
, spid int 
, clipid int
, host nvarchar(40) COLLATE  database_default
)
END

/* ######################################### START MAIN PROCEDURE HERE ########################################## */

BEGIN

INSERT INTO @TmpTrace
SELECT 
ObjectName
, DatabaseName
, StartTime
, EventClass
, EventSubClass
, ObjectType
, ServerName
, LoginName
, ApplicationName
, 'temp'
, spid
, ClientProcessID
, HostName
FROM ::fn_trace_gettable( @TraceFileName, DEFAULT )
WHERE 
objecttype not in (21587)
AND EventClass in (46,47,164) 
AND EventSubclass = 0 
AND LoginName NOT IN ('NT AUTHORITY\NETWORK SERVICE', 'sa') 
AND DatabaseID <> 2
AND StartTime NOT IN 
(
SELECT Captured FROM  
[SQLDEV01].[TEST_R_D].dbo.DBSchemaChangeMonitor
) -- >> Omit previously inserted recs

SET @Cnt = @@ROWCOUNT

/* Process Records */
IF @Cnt > 0
BEGIN
/* Update events to be understandable */
UPDATE @TmpTrace 
SET ddl_operation = 'CREATE' 
WHERE event_class = 46

UPDATE  @TmpTrace  
SET ddl_operation = 'DROP' 
WHERE event_class = 47

UPDATE  @TmpTrace  
SET ddl_operation = 'ALTER' 
WHERE event_class = 164

/* Fetch the Results */
INSERT INTO [SQLDEV01].[TEST_R_D].dbo.DBSchemaChangeMonitor 
(
[Captured]
,[Server]
,[DBName]
,[Command]
,[Application]
,[spid]
,[Object]
,[Login]
,[ClientProcessID]
,[WorkStation]
,[InsertedOn]
)
SELECT
start_time , 
server_name, 
database_name, 
ddl_operation, 
'[' + CAST(object_type as varchar (6)) + '] ' + application_name,
spid, 
obj_name,
login_name,
clipid,
host, 
CONVERT(varchar(10), GETDATE(), 111)
FROM @TmpTrace 
WHERE --object_type not in (21587) -->> This is Table Statistics (not needed)
obj_name NOT IN ('Tables you want to omit') -->> Remove if you want ALL tables
ORDER BY 
server_name, start_time DESC
END
END

/* ########################################## END MAIN PROCEDURE HERE ########################################### */
GO

https://msdn.microsoft.com/en-us/library/ff650699.aspx

/*

When you begin profiling, the following guidelines will help you use the tool most effectively:

• Filter the captured information. In a high-traffic database, filtering enables you to capture only the required information. Consider using one of the predefined filters to:
  • Filter by ApplicationName to trace data specific to one application.
  • Filter by DatabaseName specify to your application if the server is hosting several databases.
  • Filter by Duration to measure query performance.
  • Filter by a specific object in ObjectName, and by a specific user in LoginName.
• Consider saving trace information to a file. Instead of watching the trace information on-screen, you can capture the results to a file. This technique is useful for offline analysis. You can also use captured trace information as input to the SQL Server Index Tuning Wizard or insert it into a SQL Server table.

  Note   Sending trace data directly to a SQL Server table can incur performance overhead. It is often better to trace to a file and then import the data to a table later.

  To save trace output to a file from the trace output window, click Save As on the File menu, click Trace File, and then specify a file location and a name. Trace files are given the .trc extension.

  To capture output directly to a file, specify the file name details on the General tab of the Trace Properties dialog box. You should also set the maximum file size to prevent the file from growing indefinitely. Then when the file reaches the maximum size, a new file will be created.

• Consider saving data to a file before sending it to a database table. The interception mechanism for capturing information can itself slow the application, depending on the volume of data captured and the number of client requests. Capturing profiler data to a database table is relatively slow compared to saving to a file. If you need to save data to database table, consider saving the data to a file first and then importing it to a database table. Doing so reduces the trace overhead and improves trace performance.

  The following statement creates a table named 1108 in the current database and imports the trace information from MyTrace.trc into that table.

*/

Hi Rajkumar,

Based on my research on profiler trace file, the column EventSequence may play some trick here. I was testing in the scenario as below.

Profile events selected:

• Objects:objects:altered, objects:created, objects:deleted
• TSQL: SQL:BatchCompleted, SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtStarting

After starting the above tracing, the below statements are tested.

 CREATE table  t122(id int);

 ALTER TABLE t122
 ADD NAME19 VARCHAR(50);

 DROP TABLE T122;

SELECT tg.eventclass,te.name,tg.EventSequence,tg.textdata FROM sys.fn_trace_gettable('C:\1.trc',default) tg
						JOIN sys.trace_events te ON tg.eventclass=te.trace_event_id
where loginname='mylogin' and databaseid=8 and  EVENTCLASS IN (40,41,46,47,164)
order by EventSequence